# 智能推荐模块

# -*- coding: utf-8 -*-
import mysql.connector
import pandas as pd
import numpy as np
from openai import OpenAI
from sklearn.metrics.pairwise import cosine_similarity
from datetime import datetime
import sys
import io
from urllib.parse import quote_plus

password = "SYJwww62442@"
encoded_password = quote_plus(password)

# 设置标准输出的编码为UTF-8
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

# 设置 OpenAI API 客户端
client = OpenAI(
    base_url="https://api.openai-proxy.org/v1",
    api_key="sk-0CCtNIrBc0s3GJz1JI5GSSh9mZh7ehTARtT6kIY18DlYxlfD",
)

# 全局配置
EMBEDDING_MODEL = "text-embedding-3-small"
embedding_cache = {}

from sqlalchemy import create_engine


def fetch_user_items(user_id):
    """获取用户所有衣物及类别信息（包含创建时间）"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        query = """
        SELECT c1.favorites_id, c1.description, c1.created_at, c1.category_id, cc.name as category 
        FROM favorites c1
        JOIN clothes_categories cc ON c1.category_id = cc.category_id
        WHERE c1.user_id = %s
        """
        user_df = pd.read_sql(query, engine, params=(user_id,))
        return user_df
    except Exception as e:
        print(f"数据库错误：{e}")
        return pd.DataFrame()


def fetch_category_items(category_id):
    """获取指定类别的所有商品"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        query = """
        SELECT clothes_id, description 
        FROM clothes1 
        WHERE category_id = %s
        """
        items_df = pd.read_sql(query, engine, params=(category_id,))
        print(f"获取类别 {category_id} 的 {len(items_df)} 件商品")
        return items_df
    except Exception as e:
        print(f"数据库错误：{e}")
        return pd.DataFrame()


def generate_embedding(text):
    """生成单个文本嵌入"""
    if not text:
        return None

    try:
        # 检查缓存
        if text in embedding_cache:
            return embedding_cache[text]

        response = client.embeddings.create(
            model=EMBEDDING_MODEL,
            input=text
        )
        embedding = np.array(response.data[0].embedding)
        embedding_cache[text] = embedding
        return embedding
    except Exception as e:
        print(f"嵌入生成失败：{e}")
        return None


def check_user_recommendations(user_id):
    """检查用户是否已有推荐结果"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        query = """
        SELECT COUNT(*) as count
        FROM recommendations
        WHERE user_id = %s
        """
        result = pd.read_sql(query, engine, params=(user_id,))
        return result.iloc[0]['count'] > 0
    except Exception as e:
        print(f"数据库错误：{e}")
        return False


def get_user_existing_clothes(user_id):
    """获取用户已有的衣服"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        query = """
        SELECT clothes_id
        FROM recommendations
        WHERE user_id = %s
        """
        result = pd.read_sql(query, engine, params=(user_id,))
        return set(result['clothes_id'])
    except Exception as e:
        print(f"数据库错误：{e}")
        return set()


def get_new_clothes(user_id, existing_clothes):
    """获取新添加的衣服"""
    user_df = fetch_user_items(user_id)
    if user_df.empty:
        return pd.DataFrame()

    new_clothes = user_df[~user_df['clothes_id'].isin(existing_clothes)]
    return new_clothes


def category_based_recommendation(user_id, top_n=5, decay_rate=0.1, new_clothes=None):
    """基于类别和时间加权的推荐系统"""
    if new_clothes is None:
        user_df = fetch_user_items(user_id)
    else:
        user_df = new_clothes

    if user_df.empty:
        return {}

    recommendations = {}
    for category_id, group in user_df.groupby('category_id'):
        category_name = group.iloc[0]['category']
        current_time = datetime.now()

        # 提取创建时间并计算时间间隔（天数）
        create_times = pd.to_datetime(group['created_at']).dt.tz_localize(None)
        ages_days = [(current_time - ct).days for ct in create_times]

        # 计算权重（指数衰减）
        weights = [np.exp(-decay_rate * age) for age in ages_days]

        # 生成用户嵌入向量（过滤无效值）
        user_embeddings = []
        for desc in group['description']:
            try:
                emb = generate_embedding(desc)
                if emb is not None:
                    user_embeddings.append(emb)
            except Exception as e:
                print(f"生成嵌入向量失败: {e}")
                continue

        if not user_embeddings:
            continue

        # 计算加权平均嵌入向量
        avg_user_emb = np.average(user_embeddings, axis=0, weights=weights[:len(user_embeddings)])

        # 获取同类商品
        items_df = fetch_category_items(category_id)
        if items_df.empty:
            continue

        # 批量生成商品嵌入向量
        item_embeddings = []
        valid_items = []
        for idx, row in items_df.iterrows():
            try:
                emb = generate_embedding(row['description'])
                if emb is not None:
                    item_embeddings.append(emb)
                    valid_items.append(row)
            except Exception as e:
                print(f"生成商品嵌入向量失败: {e}")
                continue

        if not item_embeddings:
            continue

        # 计算相似度
        similarities = cosine_similarity([avg_user_emb], item_embeddings)[0]

        # 创建临时DataFrame并排序
        temp_df = pd.DataFrame({
            'clothes_id': [item['clothes_id'] for item in valid_items],
            'similarity': similarities
        })
        temp_df = temp_df.sort_values('similarity', ascending=False)

        # 存储商品ID和相似度
        recommendations[category_name] = list(
            zip(temp_df.head(top_n)['clothes_id'], temp_df.head(top_n)['similarity'])
        )

    return recommendations


def save_recommendations_to_db(user_id, recommendations):
    """将推荐结果存入数据库"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        conn = engine.connect()

        # 批量插入数据
        values = []
        for category, items in recommendations.items():
            # 获取类别ID
            category_query = """
                SELECT category_id 
                FROM clothes_categories 
                WHERE name = %s
            """
            category_result = pd.read_sql(category_query, engine, params=(category,))

            if category_result.empty:
                print(f"警告：找不到类别 {category} 的ID")
                continue

            category_id = int(category_result.iloc[0]['category_id'])

            for clothes_id, similarity in items:
                # 确保所有值都是 Python 原生类型
                values.append((
                    int(user_id),
                    str(category),
                    int(category_id),
                    int(clothes_id),
                    float(similarity)
                ))

        if values:
            # 使用批量插入
            query = """
            INSERT INTO recommendations (user_id, category, category_id, clothes_id, similarity)
            VALUES (%s, %s, %s, %s, %s)
            """
            conn.execute(query, values)

        conn.close()
        print("推荐结果已存入数据库")
    except Exception as e:
        print(f"数据库错误：{e}")


def get_new_clothes_list(user_id, clothes_ids):
    """获取指定衣服ID列表的详细信息"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://root:{encoded_password}@127.0.0.1:3306/stylefit"
        )
        query = """
        SELECT c1.clothes_id, c1.description, c1.created_at, c1.category_id, cc.name as category 
        FROM clothes1 c1
        JOIN clothes_categories cc ON c1.category_id = cc.category_id
        WHERE c1.user_id = %s AND c1.clothes_id IN %s
        """
        user_df = pd.read_sql(query, engine, params=(user_id, tuple(clothes_ids)))
        return user_df
    except Exception as e:
        print(f"数据库错误：{e}")
        return pd.DataFrame()


def main(user_id=None, new_clothes_ids=None):
    """执行推荐并存储结果到数据库

    Args:
        user_id: 用户ID
        new_clothes_ids: 新衣服ID列表，如果为None则表示首次调用
    """
    if user_id is None:
        print("错误：必须提供用户ID")
        return

    print(f"\n用户 {user_id} 专属推荐")

    # 检查用户是否已有推荐
    has_recommendations = check_user_recommendations(user_id)

    if not has_recommendations:
        print("用户没有推荐结果，开始全量计算...")
        rec_result = category_based_recommendation(user_id)
    else:
        print("用户已有推荐结果，检查是否有新衣服...")
        existing_clothes = get_user_existing_clothes(user_id)

        if new_clothes_ids:
            # 如果传入了新衣服ID列表，只处理这些衣服
            new_clothes = get_new_clothes_list(user_id, new_clothes_ids)
        else:
            # 否则获取所有新衣服
            new_clothes = get_new_clothes(user_id, existing_clothes)

        if new_clothes.empty:
            print("没有新衣服，无需重新计算推荐")
            return

        print("发现新衣服，开始增量计算...")
        rec_result = category_based_recommendation(user_id, new_clothes=new_clothes)

    # 存储推荐结果到数据库
    save_recommendations_to_db(user_id, rec_result)

    print("\n推荐结果：")
    if rec_result:
        for category, items in rec_result.items():
            print(f"\n=== {category} ===")
            for idx, (clothes_id, similarity) in enumerate(items, 1):
                print(f"{idx}. 商品ID：{clothes_id}，相似度：{similarity:.4f}")
    else:
        print("无推荐结果")


if __name__ == "__main__":
    import sys
    import ast

    if len(sys.argv) > 1:
        user_id = int(sys.argv[1])
        if len(sys.argv) > 2:
            # 尝试将第二个参数解析为衣服ID列表
            try:
                new_clothes_ids = ast.literal_eval(sys.argv[2])
                if not isinstance(new_clothes_ids, list):
                    new_clothes_ids = [new_clothes_ids]
            except:
                new_clothes_ids = None
        else:
            new_clothes_ids = None
        main(user_id, new_clothes_ids)
    else:
        print("错误：必须提供用户ID")
